package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.process.dao.PartNumber;
import com.scalar.chiptrack.operations.process.Lot;
import com.scalar.chiptrack.operations.dataentry.forms.BOMDataEntryForm;
import com.scalar.chiptrack.operations.dataentry.BOMLotBinData;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

import java.util.ArrayList;
import java.util.Iterator;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Created by Sudhakar.
 * Date: May 12, 2005
 * Time: 11:00:02 AM
 */
public class BOMDataEntryDAO
{
    public BOMDataEntryDAO()
    {
    }

    public static ArrayList getPartNumber() throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList partNumberList = null;

        try
        {
            partNumberList = new ArrayList();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "select part_number,Process_Step.ProcessStep_ID from product,Process_Step  where p_type='BOM' and product.processStep_id=Process_Step.ProcessStep_ID and process_step.ps_type_name = 'SORT' Order By part_number" );

            rs = pstmt.executeQuery();
            PartNumber partNumber = new PartNumber();

            while ( rs.next() )
            {
                partNumber = new PartNumber();
                partNumber.setPartNumber( rs.getString( 1 ) );
                partNumber.setProcessStepId( rs.getString( 2 ) );

                partNumberList.add( partNumber );
            }
        }
        catch ( Exception sql )
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Part Number ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return partNumberList;
    }


    // To get the the Locations
    public static ArrayList getRunningLotNumbersByPSTypeName ( String processStepId, String locationId, String psTypeName ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList ();
        Lot lot = null;

        try
        {
            psTypeName = psTypeName + "%";

            conn = DataSource.getConnection ();
            pstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.dataentry.getRunningLotNumbersByPSType" ) );
            pstmt.setString ( 1, processStepId );
            pstmt.setString ( 2, locationId );
            pstmt.setString ( 3, psTypeName );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                lot = new Lot ( rs.getString ( 1 ), rs.getString ( 2 ) );
                lot.setStatus ( rs.getString ( 3 ) );
                lotNumberList.add ( lot );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get Ruuning Lot Numbers from database", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try {     rs.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( pstmt != null )
            {
                try {     pstmt.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( conn != null )
            {
                try {    conn.close ();     }     catch ( SQLException ignore )      {      }
            }
        }
        return lotNumberList;
    }
    //Saving in to Inventory tables
    public static void saveInventoryLot (DBTransaction dbTransaction, Iterator inventoryLotIterator, String inventoryType, String vendorAdaptorName) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.createUpdateSortLotDataEntry"));
            BOMDataEntryForm bomDataEntryForm = null;
            while (inventoryLotIterator.hasNext())
            {
                bomDataEntryForm = (BOMDataEntryForm) inventoryLotIterator.next();

                pstmt.setString (1, bomDataEntryForm.getPartNumber());
                pstmt.setString (2, bomDataEntryForm.getLotNumber());
                pstmt.setString (3, bomDataEntryForm.getLotStatus());
                pstmt.setString (4, bomDataEntryForm.getQtyIn());
                pstmt.setString (5, null);
                pstmt.setString (6, bomDataEntryForm.getDateIn());
                pstmt.setString (7, null);
                pstmt.setString (8, null);
                pstmt.setString (9, vendorAdaptorName);
                pstmt.setString (10, bomDataEntryForm.getQtyOut());
                pstmt.setString (11, bomDataEntryForm.getDateOut());
                pstmt.setString( 12, inventoryType);
                pstmt.setString (13, null );
                pstmt.setString (14, null );
                pstmt.setString (15, null );
                pstmt.setString (16, null );
                pstmt.setString (17, bomDataEntryForm.getDateCode());
                pstmt.setString (18,bomDataEntryForm.getVendorLot());
                pstmt.setString (19,bomDataEntryForm.getComments());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save BOM Lot Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }


            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }
    }

    public static void saveBinLotData( DBTransaction dbTransaction, ArrayList binLotData, int lotId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("DELETE FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = ?");
            pstmt.setInt( 1, lotId );
            pstmt.executeUpdate();
            pstmt.close();

            pstmt = conn.prepareStatement ("{CALL SP_SAVE_BOM_INVENTORY(?, ?, ?, ?, ?)}");
            Iterator binLotDataIter = binLotData.iterator();
            while (binLotDataIter.hasNext())
            {
                BOMLotBinData bomLotBinData = (BOMLotBinData) binLotDataIter.next();
                int bin1Qty,bin1BalanceQty;
                try
                {
                    bin1Qty = Integer.parseInt(bomLotBinData.getBin1Qty());
                }
                catch( NumberFormatException e)
                {
                    bin1Qty = 0;
                }
                try
                {
                    bin1BalanceQty = Integer.parseInt(bomLotBinData.getBin1BalanceQty() );
                }
                catch( NumberFormatException e)
                {
                    bin1BalanceQty = 0;
                }
                pstmt.setInt (1, lotId);
                pstmt.setString (2, bomLotBinData.getWaferId());
                pstmt.setInt (3, bin1Qty);
                pstmt.setInt (4, bin1BalanceQty);
                pstmt.setInt (5, (bomLotBinData.isScrap()==true) ? 0 : 1 );
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save BOM Lot Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }


            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }
    }
    /**
     * This method is to get the BOM inventory wafer wise for the specified lot.
     * @param lotId
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadBomLotBinInfo( int lotId ) throws ScalarException
    {
        ArrayList bomLotList    = new ArrayList();
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ("SELECT WAFER_ID, BIN_1, BIN_1_BALANCE_QTY FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = ? ORDER BY CAST(WAFER_ID AS INT)");
            pstmt.setInt( 1, lotId );
            rset = pstmt.executeQuery();
            int serielNo = 1;

            while( rset.next() )
            {
                BOMLotBinData bomLotBin = new BOMLotBinData();
                bomLotBin.setSerialNo( serielNo++ );
                bomLotBin.setWaferId( rset.getString( "WAFER_ID" ) ) ;
                bomLotBin .setBin1Qty( rset.getString( "BIN_1" ) );
                bomLotBin .setBin1BalanceQty( rset.getString( "BIN_1_BALANCE_QTY" ) );
                if( "0".equalsIgnoreCase( rset.getString( "BIN_1_BALANCE_QTY" ) ) )
                {
                    bomLotBin.setScrap( true );
                }
                bomLotList.add( bomLotBin);
            }
        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }

        return bomLotList;
    }
    /**
     * This method is to check wther any BOM inventory is pulled for the issued or approved assembly release.
     * @param lotId
     * @param bomLotList
     * @throws ScalarException
     */

    public static void checkAssemblyReleaseExistsForBOMLot( int lotId, ArrayList bomLotList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ("select BRLT.ASSEMBLY_RELEASE_ID,BRLT.WAFER_ID from BOM_RELEASE_LOT_TABLE BRLT, ASSM_RELEASE_TABLE ASM, ASSM_RELEASE_LOT_TABLE ARLT WHERE BRLT.ASSEMBLY_RELEASE_ID = ASM.ASSEMBLY_RELEASE_ID AND ARLT.ASSEMBLY_RELEASE_ID = BRLT.ASSEMBLY_RELEASE_ID AND (ASM.STATUS = 'Issued' OR ASM.STATUS = 'Approved') AND BRLT.LOT_ID = ? AND BRLT.WAFER_ID = ?");
            Iterator binLotDataIter = bomLotList.iterator();
            while (binLotDataIter.hasNext())
            {
                BOMLotBinData bomLotBinData = (BOMLotBinData) binLotDataIter.next();
                pstmt.setInt( 1, lotId );
                pstmt.setString( 2, bomLotBinData.getWaferId() );
                rset = null;
                rset = pstmt.executeQuery();
                if( rset.next() )
                {
                    bomLotBinData.setDisabled( true );
                }
            }
        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }

    }

    public static Lot getBOMInventoryLotData ( String lotId ) throws ScalarException
    {
         Connection conn = null;
         ResultSet rs = null;
         PreparedStatement pstmt = null;
         Lot lot = new Lot ();
         try
         {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ("select Lot_Number, Lot_Current_Status, IL_Qty_In, IL_Qty_Out, convert(varchar,IL_Date_In,101) IL_Date_In, convert(varchar,IL_Date_Out,101) IL_Date_Out, IL_Vendor_Lot_Number, Lot_Date_Code, Lot_Comments,Is_Hold from Lot_Table, Inventory_Lot where Lot_Table.Lot_id = Inventory_Lot.Lot_id and Lot_Table.Lot_id = ?");
            pstmt.setString ( 1, lotId );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                lot.setLotNumber    ( rs.getString ( "Lot_Number" ) );
                lot.setStatus       ( rs.getString ( "Lot_Current_Status" ) );
                lot.setQuantityIn   ( rs.getString ( "IL_Qty_In" ) );
                lot.setQuantityOut  ( rs.getString ( "IL_Qty_Out" ) );
                lot.setDateIn       ( rs.getString ( "IL_Date_In" ) );
                lot.setDateOut      ( rs.getString ( "IL_Date_Out" ) );
                lot.setVendorLot	( rs.getString ( "IL_Vendor_Lot_Number" ) );
                lot.setDateCode     ( rs.getString ( "Lot_Date_Code" ) );
                lot.setComments 	( rs.getString ( "Lot_Comments" ) );
                lot.setHold         ( ( rs.getBoolean( "Is_Hold" ) ) ? false : true );
            }

         }
         catch ( Exception sqle )
         {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get mark and pack lot data from database", sqle );
         }
         finally
         {
             if (rs != null)
             {
              try { rs.close(); } catch (SQLException ignore){}
             }
             if (pstmt != null)
             {
                try { pstmt.close(); } catch (SQLException ignore){}
             }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
         }
         return lot;
    }
    public static void saveInventoryLotForFG (DBTransaction dbTransaction, Iterator inventoryLotIterator, String inventoryType, String vendorAdaptorName) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveInventoryLot.cavium"));
            BOMDataEntryForm bomDataEntryForm = null;
            while (inventoryLotIterator.hasNext())
            {
                bomDataEntryForm = (BOMDataEntryForm) inventoryLotIterator.next();

                pstmt.setString (1, bomDataEntryForm.getPartNumber());
                pstmt.setString (2, bomDataEntryForm.getLotNumber());
                pstmt.setString (3, bomDataEntryForm.getQtyIn());
                pstmt.setString (4, bomDataEntryForm.getQtyOut());
                pstmt.setDate   (5, StringUtils.getSQLDate(bomDataEntryForm.getDateIn() , null));
                pstmt.setDate   (6, StringUtils.getSQLDate(bomDataEntryForm.getDateOut(), null));
                pstmt.setString (7, inventoryType);
                pstmt.setString (8, vendorAdaptorName);
                pstmt.setString (9, bomDataEntryForm.getDateCode());
                pstmt.setString (10,bomDataEntryForm.getVendorLot());
                pstmt.setString (11,bomDataEntryForm.getComments());
                pstmt.setInt(12, 0);
                pstmt.setString(13, "");

                pstmt.addBatch();
            }
            pstmt.executeBatch();
            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Inventory Lot Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }


            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }
    }
}
